# coding:utf8
import sys
import pymysql as MySQLdb
import datetime
import pandas as pd
import numpy as np
import os
import stat

host = '127.0.0.1'
user = 'root'
pwd = '123456'
db = 'll_newscms'
sql = 'SELECT lr.ll_aid , um.ll_age,um.ll_sex, um.ll_area,a.ll_cid ,lr.ll_click from ll_lrtrain as lr LEFT JOIN ll_article as a ON lr.ll_aid=a.ll_aid LEFT JOIN ll_usermsg as um ON lr.ll_uid=um.ll_uid WHERE lr.ll_aid!=0 AND a.ll_cid is NOT null AND um.ll_age is NOT null AND um.ll_sex is NOT null AND um.ll_area is NOT null'
# sheet_name = 'building'



conn = MySQLdb.connect(host,user,pwd,db,charset='utf8')
cursor = conn.cursor()
count = cursor.execute(sql)
print(count)

cursor.scroll(0,mode='absolute')
results = cursor.fetchall()
fields = cursor.description

datapath="/home/lili/project/recommend/ll_newscms/public/data/lr"
file="/home/lili/project/recommend/ll_newscms/public/data/lr/train.txt"



if not os.path.exists(datapath) :
    os.makedirs(datapath,0777)

if not os.path.exists(file):
    os.mknod(file,0777)


os.system('chmod 777 -R '+datapath)


np_data = np.array(results)
# print results
# print(np_data)
save = pd.DataFrame(np_data, columns = ['ll_aid','ll_age','ll_sex','ll_area','ll_cid','label'])
save.to_csv(file,index=False)  #index=False,header=False表示不保存行索引和列标题




